package com.itestor.iptv.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

/**
 * 初始化数据库,添加必要的数据，每次系统启动时会调用init()方法完成数据库的初始化工作，特别适合于配合测试.
 * 对于小数据量的初始化，建议采用编程的方式实现，这样可以省却为不同数据库写特定sql脚本的事情
 * 对于大批数据的初始化，可以考虑采用sql脚本来实现（为不同数据库写不同脚本，命名为ORACAL_XX.sql,HSQL_XX,sql等）
 * @author spring
 */
public class DBInitiator {
    /**
     * Logger for this class.
     */
    private static final Logger logger = Logger.getLogger(DBInitiator.class);

    /** Hibernate SessionFactory. */
    private SessionFactory sessionFactory;

    /**
     * 加载的SQL 文件.
     */
    private String[] sqlFiles;

    /**
     * 该方法完成数据准备的工作.
     */
    public void init() {
        // 从数据库加载初始化SQL脚本
        initDBfromSqlFiles();

    }

    private void initDBfromSqlFiles() {
        for (String sqlFile : sqlFiles) {
            logger.info("read sql file " + sqlFile);
            InputStream inStream = getClass().getClassLoader()
                    .getResourceAsStream(sqlFile);
            logger.info("execute sql file " + sqlFile);
            initDBfromSqlFile(inStream);
        }

    }

    /**
     * 加载数据库脚本初始化数据库.
     * @param inStream inStream
     * @return String 执行结果
     */
    @SuppressWarnings("deprecation")
    public String initDBfromSqlFile(InputStream inStream) {
        String result = null;
        String[] sqls = readSQLFile(inStream);
        Session session = sessionFactory.openSession();
        Transaction trans = session.beginTransaction();
        Statement stmt = null;
        int curSqlIndex = 0;
        String curSql = null;
        try {
            stmt = session.connection().createStatement();
            int updateCount = 0;
            if (sqls != null) {
                for (int i = 0; i < sqls.length; i++) {
                    curSqlIndex = i;
                    curSql = sqls[i];
                    updateCount += stmt.executeUpdate(curSql);
                }
            }
            logger.info("  affected rows  " + updateCount);
            trans.commit();
            result = "success!<br>" + "  affected rows  " + updateCount;
        } catch (Exception err) {
            logger.error(" error occured  cur sql " + curSqlIndex + " "
                    + curSql, err);
            result = "error!<br>" + " error occured  cur sql: <br>" + curSql;
            trans.rollback();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    logger.warn("close stmt err ", e);
                }
            }
            session.close();
        }
        return result;
    }

    /**
     * 将SQL资源文件读到String[].
     * @param resourceName sql resouce
     * @return String[]
     */
    private String[] readSQLFile(InputStream inStream) {
        ArrayList<String> sqlAry = new ArrayList<String>();
        if (inStream == null) {
            logger.warn("sql resouce file not exits ");
            return new String[0];
        }
        LineNumberReader lineReader = null;
        try {
            lineReader = new LineNumberReader(new InputStreamReader(inStream,
                    "UTF-8"));
            String sql = lineReader.readLine();
            while (sql != null) {
                sql = sql.trim();
                if (!"".equals(sql) && sql.charAt(0) != '#') {
                    sqlAry.add(sql);

                }
                sql = lineReader.readLine();
            }
            return sqlAry.toArray(new String[sqlAry.size()]);
        } catch (IOException e) {
            logger.error("read sql file err", e);
            return new String[0];
        } finally {

            // 关闭Stream
            if (lineReader != null) {
                try {
                    lineReader.close();
                } catch (IOException e) {
                    logger.warn("close file err ", e);
                }
            }

        }
    }

    /**
     * @param pSessionFactory the pSessionFactory to set
     */
    public void setSessionFactory(SessionFactory pSessionFactory) {
        this.sessionFactory = pSessionFactory;
    }

    /**
     * @param pSqlFiles the sqlFiles to set
     */
    public void setSqlFiles(String[] pSqlFiles) {
        this.sqlFiles = pSqlFiles;
    }

}
